﻿

CREATE proc [Projects].[GetPurchaserNamesCount]
@OrgKey		int,
@Year		int
as
begin
	set nocount on

	declare @OrgName nvarchar(50),
			@countNames int,
			@weiter bit = 0,
			@ShortName nvarchar(100),
			@i int,
			@ret nvarchar(10)

	select @OrgName = [Name] from dbo.Organisation where OrgKey = @OrgKey

	select	@ShortName = p.ShortName
	from	Projects.datProjects p
	where	p.PurchaserID = @OrgKey

	--select @ShortName 'ShortName'

	if LEN(@ShortName) > 0
	begin
		set @i = LEN(@ShortName) - 1
		while (@weiter = 0)
		begin
			if substring(@ShortName, @i, 1) = 'p'
			begin
				set @ret = substring(@ShortName, @i - 1, 1)
				set @weiter = 1
			end
			set @i -= 1
			if @i = 0
			begin
				set @weiter = 1
			end
		end
	end
	
	if @ret != ''
	begin
		select cast(@ret as int) 'NamesCount', 0 'New'
	end
	else
	begin
		select	count(*) + 1 'NamesCount', 1 'New'
		from	dbo.Organisation o
		where	o.OrgKey in (
					select	p.PurchaserID
					from	Projects.datProjects p
					where	year(p.FirstContactDate) = @Year
					)
				and o.[Name] = @OrgName
				and o.OrgKey != @OrgKey
	end

	

	return 0
end
GO


